<?php

	include("koneksi.php");
	//selesai
	function Selesai($id){
		if(updateStatusJaprak($id)){
		$q_asisten = "UPDATE config SET japrak=0,username='admin',password='admin',matakuliah='-',kelas='-' WHERE id=1";
		$h_asisten = mysql_query($q_asisten) or exit("Error query : <b>".$q_asisten."</b>.");	
		return $h_asisten;
		}
	}

	function updateStatusJaprak ($id){
		$q_asisten = "UPDATE japrak SET status_poling='SUDAH' WHERE id_japrak=".$id;
		$h_asisten = mysql_query($q_asisten) or exit("Error query : <b>".$q_asisten."</b>.");	
		return $h_asisten;
	}
	
	
	//cek pengunjung cp
	function cekServer(){
		if($_SERVER['REMOTE_ADDR']=="127.0.0.1" || $_SERVER['REMOTE_ADDR']=="::1"){
			return true;
		}
			return false;
	}
	//memasukan ip ke db log
	function sendLogClient($japrak,$ip){
		$que="INSERT INTO log(japrak,ip) VALUES (".$japrak.",'".$ip."')";
		mysql_query($que) or exit("Error query : <b>".$que."</b>.");	
	}

    function getIP() {
        if (getenv("HTTP_CLIENT_IP") && strcasecmp(getenv("HTTP_CLIENT_IP"), "unknown")) {
            $ip = getenv("HTTP_CLIENT_IP");
        } else if (getenv("HTTP_X_FORWARDED_FOR") && strcasecmp(getenv("HTTP_X_FORWARDED_FOR"), "unknown")) {
            $ip = getenv("HTTP_X_FORWARDED_FOR");
        } else if (getenv("REMOTE_ADDR") && strcasecmp(getenv("REMOTE_ADDR"), "unknown")) {
            $ip = getenv("REMOTE_ADDR");
        } else if (isset($_SERVER['REMOTE_ADDR']) && $_SERVER['REMOTE_ADDR'] && strcasecmp($_SERVER['REMOTE_ADDR'], "unknown")) {
            $ip = $_SERVER['REMOTE_ADDR'];
        } else {
            $ip = "unknown";
        }
        return($ip);
    }


	function getConfig(){
		$q_sistem = "SELECT * FROM config ";
		$h_sistem = mysql_query($q_sistem) or exit("Error query : <b>".$q_sistem."</b>.");
		$d_sistem = mysql_fetch_array($h_sistem);
		return $d_sistem;
	}	
	
	function getDetailJaprak($id){
		$q_sistem = "SELECT j.id_japrak,nama_kelas,nama_matkul,j.id_matkul,jam_mulai,jam_selesai,hari from japrak j inner join kelas k on j.id_kelas=k.id_kelas inner join matkul m on j.id_matkul=m.id_matkul inner join sesi s on j.id_sesi=s.id_sesi where id_japrak=".$id;
		$h_sistem = mysql_query($q_sistem) or exit("Error query : <b>".$q_sistem."</b>.");
		$d_sistem = mysql_fetch_array($h_sistem);
		return $d_sistem;
	}	
	
	function getDetailAsisten($id){
		$q_sistem = "SELECT nama_asisten,id_asisten,nim_asisten from asisten where id_asisten=".$id;
		$h_sistem = mysql_query($q_sistem) or exit("Error query : <b>".$q_sistem."</b>.");
		$d_sistem = mysql_fetch_array($h_sistem);
		return $d_sistem;
	}	
	
	function getOneJaprak($idjaprak){
		$q_data = "SELECT * FROM japrak where id_japrak=".$idjaprak;
		$h_data = mysql_query($q_data) or exit("Error query : <b>".$q_data."</b>.");
		$d_data = mysql_fetch_array($h_data);
		return $d_data;
	}	
	function getCountPCJaprak($id){
		$q_count = "SELECT COUNT(DISTINCT ip_voter) as TOTAL FROM vote WHERE id_japrak=".$id;
		$h_count = mysql_query($q_count) or exit("Error query : <b>".$q_count."</b>.");
		$d_count = mysql_fetch_array($h_count);
		return $d_count;
	}	
	function getCountPC(){
		$q_count = "SELECT COUNT(DISTINCT ip_voter) as TOTAL FROM vote ";
		$h_count = mysql_query($q_count) or exit("Error query : <b>".$q_count."</b>.");
		$d_count = mysql_fetch_array($h_count);
		return $d_count;
	}	

    function getOneAsisten($id){
		$q_oneas = "SELECT * FROM asisten WHERE idAsisten = '".$id."' ";
		$h_oneas = mysql_query($q_oneas) or exit("Error query : <b>".$q_oneas."</b>.");
		$d_oneas = mysql_fetch_array($h_oneas);
		return $d_oneas;
	}
	function getIdJaprak($id_hari,$id_sesi,$id_matkul,$id_kelas){
		$que = "SELECT id_japrak FROM japrak"
		 	." WHERE hari='".$id_hari."' and id_sesi=".$id_sesi." and id_matkul=".$id_matkul." and id_kelas=".$id_kelas;
			$data="";
			$runs=mysql_query($que)or exit($que) ;
	
	if(mysql_num_rows($runs)>0){
		$data=mysql_fetch_array($runs);
		$out=$data[0];
			}else{
		$out="gagal";
		}
		
		return $out;
	}
	function getAllAsisten(){
		$config=getConfig();
		$q_asisten = "SELECT * FROM asisten a inner join ngasisten n on a.id_asisten=n.id_asisten where id_japrak=".$config['japrak'];
		$h_asisten = mysql_query($q_asisten) or exit("Error query : <b>".$q_asisten."</b>.");	
		return $h_asisten;
	}	
	
	function getAllAsistenOlah($idjaprak){
		$q_asisten = "SELECT * FROM asisten a inner join ngasisten n on a.id_asisten=n.id_asisten where id_japrak=".$idjaprak;
		$h_asisten = mysql_query($q_asisten) or exit("Error query : <b>".$q_asisten."</b>.");	
		return $h_asisten;
	}	
	function getNgasistenJaprak($id){
		$que = "SELECT * FROM asisten a inner join ngasisten n on a.id_asisten=n.id_asisten where id_japrak=".$id;
		$runs=mysql_query($que)or exit($que) ;
		$i=0;
		while($data=mysql_fetch_assoc($runs)){
			$datas[$i]=$data;
			$i++;
		}
		if(isset($datas)){
			return $datas;
		}else{
			return 0;
		}
	}	
	function getAllAsistenPerMatkul($id){
		$que= "SELECT * FROM asisten a inner join ngasisten n on a.id_asisten=n.id_asisten
			   inner join japrak j on n.id_japrak=j.id_japrak	WHERE id_matkul=".$id." GROUP BY a.id_asisten ORDER BY nama_asisten ASC";
		$runs=mysql_query($que)or exit($que) ;
		$i=0;
		while($data=mysql_fetch_assoc($runs)){
			$datas[$i]=$data;
			$i++;
		}
		if(isset($datas)){
			return $datas;
		}else{
			return 0;
		}
	}
	
	function getAllKelas(){
		$que= "SELECT * FROM kelas";
		$runs=mysql_query($que)or exit($que) ;
		$i=0;
		while($data=mysql_fetch_assoc($runs)){
			$datas[$i]=$data;
			$i++;
		}
		if(isset($datas)){
			return $datas;
		}else{
			return 0;
		}
	}
	
	function getAllSesi(){
		$que= "SELECT * FROM sesi";
		$runs=mysql_query($que)or exit($que) ;
		$i=0;
		while($data=mysql_fetch_assoc($runs)){
			$datas[$i]=$data;
			$i++;
		}
		if(isset($datas)){
			return $datas;
		}else{
			return 0;
		}
	}
	
	function getAllMatkul(){
		$que= "SELECT * FROM matkul";
		$runs=mysql_query($que)or exit($que) ;
		$i=0;
		while($data=mysql_fetch_assoc($runs)){
			$datas[$i]=$data;
			$i++;
		}
		if(isset($datas)){
			return $datas;
		}else{
			return 0;
		}
	}
	

    function dropVote(){
		$q_drop = "DELETE FROM vote";
		$h_drop = mysql_query($q_drop) or exit("Error query : <b>".$q_drop."</b>.");	
		return $h_drop;
	}	

    function getAllVoteJaprak($id){
		$q_asisten = "SELECT * FROM vote v JOIN asisten a ON a.id_asisten = v.id_asisten WHERE id_japrak=".$id." ORDER BY v.id_asisten ASC";
		$h_asisten = mysql_query($q_asisten) or exit("Error query : <b>".$q_asisten."</b>.");	
		return $h_asisten;
	}
	 function getAllVote(){
		$q_asisten = "SELECT * FROM vote v JOIN asisten a ON a.id_asisten = v.id_asisten  ORDER BY v.id_asisten ASC";
		$h_asisten = mysql_query($q_asisten) or exit("Error query : <b>".$q_asisten."</b>.");	
		return $h_asisten;
	}
	
	 function updateJadwalNgasisten($idjaprak,$id_old,$id){
		$q_asisten = "UPDATE ngasisten SET id_asisten=".$id." WHERE id_japrak=".$idjaprak." AND id_asisten=".$id_old;
		$h_asisten = mysql_query($q_asisten) or exit("Error query : <b>".$q_asisten."</b>.");	
		return $h_asisten;
	}	
	
    function updateAsisten($id,$nama,$panggilan,$nim,$foto){
		$q_asisten = "UPDATE asisten SET Nama='".$nama."',NIM='".$nim."',Foto='".$foto."',namaPanggilan='".$panggilan."' WHERE idAsisten = '".$id."'";
		$h_asisten = mysql_query($q_asisten) or exit("Error query : <b>".$q_asisten."</b>.");	
		return $h_asisten;
	}	    

    function updateConfig($username, $password, $matakuliah, $kelas, $pesan, $pesanDepan, $jenisPolling, $lihatPolling, $cekIP, $cekMAC,$japrak){
		$q_asisten = "UPDATE config SET username='".$username."',password='".$password."',matakuliah='".$matakuliah."',kelas='".$kelas."',pesan='".$pesan."',pesanDepan='".$pesanDepan."',jenisPolling='".$jenisPolling."',lihatHasil='".$lihatPolling."',cekIP='".$cekIP."',cekMAC='".$cekMAC."',japrak=".$japrak." WHERE id = '1'";
		$h_asisten = mysql_query($q_asisten) or exit("Error query : <b>".$q_asisten."</b>.");	
		return $h_asisten;
	}	

    function getAllAsistenById($id){
		$q_asisten = "SELECT * FROM asisten WHERE idAsisten IN (".$id.") ORDER BY idAsisten ASC";
		$h_asisten = mysql_query($q_asisten) or exit("Error query : <b>".$q_asisten."</b>.");	
		return $h_asisten;
	}

	function sendVote($id,$pesan,$total,$mac,$makul,$kelas,$det){
		$q_vote = "INSERT INTO vote VALUES('','".GetHostByName($REMOTE_ADDR)."',
		'".getIP()."','".$total."','".$pesan."','".$id."','".$mac."','".$makul."','".$kelas."','".$det."')";
		mysql_query($q_vote) or exit("Error query : <b>".$q_vote."</b>.");	
	}
	
    function sendAsisten($nama,$panggilan,$nim,$foto){
		$q_vote = "INSERT INTO asisten VALUES('','".$nama."','".$nim."','".$foto."','".$panggilan."')";
		mysql_query($q_vote) or exit("Error query : <b>".$q_vote."</b>.");	
	}

	function hapusAsisten($id){
		$q_hpsasisten = "DELETE FROM asisten WHERE idAsisten = '".$id."'";
		mysql_query($q_hpsasisten) or exit("Error query : <b>".$q_hpsasisten."</b>.");	
	}

	function checkLogin($id,$pass){
		$q_login = "SELECT * FROM config WHERE username = '".stripslashes($id)."' AND password = '".stripslashes($pass)."'";
		$h_login = mysql_query($q_login) or exit("Error query : <b>".$q_login."</b>.");	
		//$d_login = mysql_fetch_array($h_login);
		$count=mysql_num_rows($h_login);
		return $count ;
	}

    function sendVoteMulti($id,$pesan,$total,$mac,$makul,$kelas,$det,$japrak){
        $q_vote = "INSERT INTO vote VALUES('','".GetHostByName($REMOTE_ADDR)."',
		'".getIP()."','".$total."','".$pesan."',".$id.",'".$mac."','".$makul."','".$kelas."','".$det."',".$japrak.")";
		mysql_query($q_vote) or exit("Error query : <b>".$q_vote."</b>.");	
    }

    function countAsisten(){
        $q_asisten = "SELECT count(*) as 'TOTAL' FROM asisten ";
		$h_asisten = mysql_query($q_asisten) or exit("Error query : <b>".$q_asisten."</b>.");	
        $num = mysql_fetch_array($h_asisten);
        $count = $num["TOTAL"];
		return $count;
    } 

    function countAsistenById($id){
        $q_asisten = "SELECT count(*) as 'TOTAL' FROM asisten WHERE id_asisten IN (".$id.")";
		$h_asisten = mysql_query($q_asisten) or exit("Error query : <b>".$q_asisten."</b>.");	
        $num = mysql_fetch_array($h_asisten);
        $count = $num["TOTAL"];
		return $count;
    } 

    function getAsistenId(){
        $q_asisten = "SELECT idAsisten FROM asisten ORDER BY idAsisten ASC";
		$h_asisten = mysql_query($q_asisten) or exit("Error query : <b>".$q_asisten."</b>.");	
        return $h_asisten;
    }

    function getAsistenIdById($id){
        $q_asisten = "SELECT id_asisten FROM asisten WHERE id_asisten IN (".$id.") ORDER BY id_asisten ASC";
		$h_asisten = mysql_query($q_asisten) or exit("Error query : <b>".$q_asisten."</b>.");	
        return $h_asisten;
    }

    function getResults(){
        $q_result = "SELECT t.idAsisten, nim,a.nama, TRUNCATE((sum(nilai)/(SELECT count(*) from vote m where m.idAsisten = t.idAsisten)),2) as      'TOTAL' FROM vote t  JOIN asisten a ON a.idAsisten = t.idAsisten GROUP BY idAsisten";
		$h_result = mysql_query($q_result) or exit("Error query : <b>".$q_result."</b>.");	
        return $h_result;
    }
?>